package cn.myword.wordTemple.mapper;

import cn.myword.wordTemple.entity.Table;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface TableMapper extends BaseMapper<Table> {
    @Select("SELECT\n" +
            "    concat(\n" +
            "            LOWER( ( string_to_array ( COLUMN_NAME, '_' ) ) [ 1 ] ),\n" +
            "            array_to_string ( ARRAY ( SELECT INITCAP ( unnest ( string_to_array ( COLUMN_NAME, '_' ) ) ) OFFSET 1 ), '' )\n" +
            "    ) as 参数名,\n" +
            "    CASE\n" +
            "\n" +
            "        WHEN is_nullable = 'NO' THEN\n" +
            "            '是'\n" +
            "        WHEN is_nullable = 'YES' THEN\n" +
            "            '否' ELSE '未知'\n" +
            "        END AS 是否必须,\n" +
            "    col_description ( ( SELECT oid FROM pg_class WHERE relname = #{sc} AND relnamespace = ( SELECT oid FROM pg_namespace WHERE nspname = 'recording' ) ), ordinal_position ) AS 描述\n" +
            ",  CASE\n" +
            "\n" +
            "       WHEN data_type = 'varchar' THEN\n" +
            "           'String'\n" +
            "       WHEN data_type = 'double precision' THEN\n" +
            "           'Double'\n" +
            "       WHEN data_type = 'USER-DEFINED' THEN\n" +
            "           'Date'\n" +
            "       WHEN data_type = 'integer' THEN\n" +
            "           'Integer'\n" +
            "       ELSE data_type END\n" +
            "    as 类型,data_type\n" +
            "FROM\n" +
            "    information_schema.COLUMNS\n" +
            "WHERE\n" +
            "    table_name = #{sc}" +
            "  AND table_schema = 'recording';")
    public List<Table> sel(@Param("sc") String sc);
}
